const express = require("express");
const bodyParser = require("body-parser");
const mysql = require("mysql2/promise");
const app = express();

// 中间件配置
app.use(bodyParser.json());
app.use(bodyParser.urlencoded({ extended: true }));

// 数据库连接池配置
const pool = mysql.createPool({
    host: 'localhost',
    user: 'test',
    password: '123456',
    database: 'studentdb',
    port: 3307,
    connectionLimit: 10
});

// 测试数据库连接
pool.getConnection()
    .then(conn => {
        console.log("数据库连接成功");
        conn.release();
    })
    .catch(err => {
        console.error("数据库连接失败", err);
    });

// 错误处理中间件
app.use((err, req, res, next) => {
    console.error(err.stack);
    res.status(500).json({ success: false, message: "服务器内部错误" });
});

// 1. 获取所有物品
app.get("/api/items", async (req, res) => {
    try {
        const [items] = await pool.query("SELECT * FROM items");
        res.json({ success: true, data: items });
    } catch (err) {
        console.error("获取物品列表失败:", err);
        res.status(500).json({ success: false, message: "获取物品列表失败" });
    }
});

// 2. 获取单个物品
app.get("/api/items/:id", async (req, res) => {
    try {
        const { id } = req.params;
        const [item] = await pool.query("SELECT * FROM items WHERE id = ?", [id]);
        
        if (item.length === 0) {
            return res.status(404).json({ success: false, message: "物品不存在" });
        }
        
        res.json({ success: true, data: item[0] });
    } catch (err) {
        console.error("获取物品失败:", err);
        res.status(500).json({ success: false, message: "获取物品失败" });
    }
});

// 3. 添加新物品
app.post("/api/items", async (req, res) => {
    try {
        const { name, description, quantity, price, category } = req.body;
        
        // 验证输入
        if (!name || !quantity || !price) {
            return res.status(400).json({ 
                success: false, 
                message: "名称、数量和价格是必填项" 
            });
        }
        
        const [result] = await pool.execute(
            "INSERT INTO items (name, description, quantity, price, category) VALUES (?, ?, ?, ?, ?)",
            [name, description, quantity, price, category]
        );
        
        res.status(201).json({ 
            success: true, 
            message: "物品添加成功",
            id: result.insertId 
        });
    } catch (err) {
        console.error("添加物品失败:", err);
        res.status(500).json({ success: false, message: "添加物品失败" });
    }
});

// 4. 更新物品
app.put("/api/items/:id", async (req, res) => {
    try {
        const { id } = req.params;
        const { name, description, quantity, price, category } = req.body;
        
        // 检查物品是否存在
        const [check] = await pool.query("SELECT * FROM items WHERE id = ?", [id]);
        if (check.length === 0) {
            return res.status(404).json({ success: false, message: "物品不存在" });
        }
        
        // 更新物品
        await pool.execute(
            "UPDATE items SET name = ?, description = ?, quantity = ?, price = ?, category = ? WHERE id = ?",
            [name, description, quantity, price, category, id]
        );
        
        res.json({ success: true, message: "物品更新成功" });
    } catch (err) {
        console.error("更新物品失败:", err);
        res.status(500).json({ success: false, message: "更新物品失败" });
    }
});

// 5. 删除物品
app.delete("/api/items/:id", async (req, res) => {
    try {
        const { id } = req.params;
        
        // 检查物品是否存在
        const [check] = await pool.query("SELECT * FROM items WHERE id = ?", [id]);
        if (check.length === 0) {
            return res.status(404).json({ success: false, message: "物品不存在" });
        }
        
        await pool.execute("DELETE FROM items WHERE id = ?", [id]);
        
        res.json({ success: true, message: "物品删除成功" });
    } catch (err) {
        console.error("删除物品失败:", err);
        res.status(500).json({ success: false, message: "删除物品失败" });
    }
});

// 6. 物品搜索
app.get("/api/items/search", async (req, res) => {
    try {
        const { keyword, category } = req.query;
        let query = "SELECT * FROM items WHERE 1=1";
        const params = [];
        
        if (keyword) {
            query += " AND (name LIKE ? OR description LIKE ?)";
            params.push(`%${keyword}%`, `%${keyword}%`);
        }
        
        if (category) {
            query += " AND category = ?";
            params.push(category);
        }
        
        const [items] = await pool.query(query, params);
        res.json({ success: true, data: items });
    } catch (err) {
        console.error("搜索物品失败:", err);
        res.status(500).json({ success: false, message: "搜索物品失败" });
    }
});

// 启动服务器
const PORT = 3000;
app.listen(PORT, () => {
    console.log(`物品管理系统服务已启动，监听端口 ${PORT}`);
});